<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@page trimDirectiveWhitespaces="true"%>
 <%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page language="java" import="java.lang.*" %>
<%@ page import = "java.sql.SQLException" %>
<%@ page import = "com.seniorproject.aims.*" %>
<%@ page import = "java.util.List" %>
<%@page import="java.util.ArrayList"%>
<%@ page import = "org.codehaus.jackson.JsonGenerationException" %>
<%@ page import = "org.codehaus.jackson.map.JsonMappingException" %>
<%@ page import = "org.codehaus.jackson.map.ObjectMapper" %>
<%@ page import = "java.io.IOException" %>
<%@ page import = "java.io.File" %>
<%@ page import = "java.io.FileInputStream" %>
<%@ page import = "org.apache.poi.ss.usermodel.Cell" %>
<%@ page import = "org.apache.poi.ss.usermodel.Row" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFSheet" %>
<%@ page import = "org.apache.poi.xssf.usermodel.XSSFWorkbook" %>
<%@ page import = "java.util.Iterator" %>
<%@ page import = "org.apache.poi.hssf.usermodel.HSSFDateUtil" %>
<%@ page import = "java.text.SimpleDateFormat" %>
<%@ page import = "java.util.Date" %>
<%@ page import = "java.util.Properties" %>
<%@ page import = "java.io.InputStream" %>
<%@ page import = "java.sql.PreparedStatement" %>
<%@page import="java.util.regex.Pattern"%>


<% 
	//get session
	String strUser = String.valueOf(session.getAttribute("sUser"));
	HttpSession htp_session = request.getSession();
	String name = String.valueOf(session.getAttribute("name"));
	String surname = String.valueOf(session.getAttribute("surname"));	
	
	if (htp_session == null || htp_session.getAttribute("sUser") == null) {
	    // Forward the control to login.jsp if authentication fails or session expires
	    request.getRequestDispatcher("/login.jsp").forward(request,
	        response);
	}
	
	if("admin".equals(htp_session.getAttribute("sUser"))){%>
	<script>alert("This user dont have PERMISSION to access this zone.");</script>
	<meta HTTP-EQUIV="Refresh" CONTENT="0; URL=admin_home.jsp"> 	
	<%}


//set Database Connection
	String hostProps = "";
	String usernameProps  = "";
	String passwordProps  = "";
	String databaseProps = "";
	
	try {
		//get current path
		ServletContext servletContext = request.getSession().getServletContext();
		
		InputStream input = servletContext.getResourceAsStream("/properties/connectDB.properties");
		Properties props = new Properties();
		
		props.load(input);

		hostProps  = props.getProperty("host");
		usernameProps  = props.getProperty("username");
		passwordProps  = props.getProperty("password");
		databaseProps = props.getProperty("database");
	} catch (Exception e) { 
		out.println(e);  
	}
	
	// connect database
	Connection connect = null;		
	try {
		Class.forName("com.mysql.jdbc.Driver");
	
		connect =  DriverManager.getConnection("jdbc:mysql://" + hostProps  + "/" + databaseProps +
				"?user=" + usernameProps  + "&password=" + passwordProps + "&characterEncoding=tis620");
	
		if(connect != null){
			System.out.println("Database Connect Sucesses.");
		} else {
			System.out.println("Database Connect Failed.");	
		}

	} catch (Exception e) {
		out.println(e.getMessage());
		e.printStackTrace();
	}
		
		
	
		System.out.println("Active student_import_background !!");
		String file_pathx = request.getParameter("file_path");
		System.out.println("File_path background:"+file_pathx);
		int count = 0;
		int i = 0 ;
		int num_row = 0;
		int num_col =0;
		String personal_id = "";
		String title_th ="";
		String title_en = "";
		String name_th= "" ; 
		String surname_th = "";
		String name_en = ""; 
		String surname_en = "";
		String gender = ""; 
		String marital_status = "";
		String birthdate = null;
		String address = "";
		String tel = "";
		String email = "";
		String facebook = "";
		String father_name= "";
		String father_address= "";
		String father_tel= "";
		String mother_name = "";
		String mother_address = "";
		String mother_tel ="";
		String parent_name = "";
		String parent_address = "";
		String parent_tel ="";
		String student_id = "";
		String start_date="";
		String end_date=null;
		String entrance = ""; 
		String degree = "";
		String school ="";
		double gpa =0;
		String status = "";
		String lecturer_id = "" ; 
		String lecturer_name_th ="";
		String lecturer_surname_th = "";
		String department_str_input ="";
		String curriculum_str_input = "";
		///////////////////////
		String full_name_id = "";
		int faculty_id = 1;
		
		String status_id = "";
		String department_id ="";
		String entrance_id = "";
		String curriculum_id = "";
		String curriculum_year="";
		String picture = "null";
		int full_name_added = 0;
		int people_added = 0 ;
		int student_added = 0 ;
		List<String> fail_list = new ArrayList<String>();
		List<String> error_log = new ArrayList<String>();
		
		  
		java.util.Date dt = new java.util.Date();

		java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd",java.util.Locale.US);

		String date_now = sdf.format(dt);
		System.out.println("Date_now:"+date_now);
		
		String[] header_array = new  String[]{"เลขประจำตัวประชาชน","คำนำหน้า","ชื่อ","นามสกุล","ชื่อ(ภาษาอังกฤษ)",
											   "นามสกุล(ภาษาอังกฤษ)","เพศ","สถานภาพ",
											   "วันเกิด","ที่อยู่นักศึกษา","เบอร์โทรศัพท์","E-mail",
											   "Facebook","ชื่อ-สกุล(บิดา)","ที่อยู่บิดา","เบอร์โทรศัพท์บิดา",
											   "ชื่อ-สกุล(มารดา)","ที่อยู่มารดา","เบอร์โทรศัพท์มารดา",
											   "ชื่อผู้ปกครอง","ที่อยู่ผู้ปกครอง","เบอร์โทรศัพท์ผู้ปกครอง",
											   "รหัสนักศึกษา","วันที่เข้าศึกษา","Entrance","ระดับการศึกษา",
											   "โรงเรียนเดิม","GPA ม.ปลาย","ชื่ออาจารย์ที่ปรึกษา","นามสกุลอาจารย์ที่ปรึกษา","สถานะ","ภาควิชา","หลักสูตร","ปีหลักสูตร"};
		System.out.println("Header_array size : "+ header_array.length);
		
		
		try{
			FileInputStream file = new FileInputStream(new File(file_pathx));
			XSSFWorkbook workbook = new XSSFWorkbook(file);
			 
            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);
 
            //Iterate through each rows one by one
            Iterator<Row> rowIterator = sheet.iterator();
            
            Iterator<Row> headerIt_row_check = sheet.iterator();
            Row row_check = headerIt_row_check.next();
            Iterator<Cell> headerIt_cell_check = row_check.cellIterator();
            
		            while (rowIterator.hasNext())
		            {	
		            	System.out.println(" //////////  ");
		            	if(count==0)
		            	{
		            		while(headerIt_cell_check.hasNext()){
		            			Cell header_cell = headerIt_cell_check.next();
		            			   switch (header_cell.getCellType())
		                           {
		                               case Cell.CELL_TYPE_NUMERIC:
		                                	  //System.out.print(header_cell.getNumericCellValue() + " ");
		                                	 // System.out.print("NUMERIC!");
		                                   break;
		                               case Cell.CELL_TYPE_STRING:
			                               	 System.out.println(header_array[i]+":"+header_cell.getStringCellValue());
			                               	 if(header_array[i].equals(header_cell.getStringCellValue()))
			                               	 {
			                               		 System.out.println("Pass:" + i);
			                               	 }
			                               	 else{
			                               		 
			                               		throw new Exception();
			                               		
			                               	 }
		                               	 
		                                   break;
		                           }// end switch
		            			   i++;
		            			
		            		}// end headerIt_cell_check.hasNext()
		            		System.out.println("");
		            		count = count +1;
		            		rowIterator.next();
		            		System.out.println("////////// END Header////////////");
		            	}// end if(count==0)
		            	
		            	try{
		            	
				                Row row = rowIterator.next();
				              
				             	
				                //For each row, iterate through all the columns
				                Iterator<Cell> cellIterator = row.cellIterator();
				                while (cellIterator.hasNext())
				                {
				                	
				                    Cell cell = cellIterator.next();
				                    //Check the cell type and format accordingly
				                    	
						                 	switch(num_col){
						               		case 0 :
							      						personal_id = String.valueOf(cell.getStringCellValue()).replaceAll("\\s+","");
							      						if(personal_id.equals("-"))
							      						{
							      							personal_id= null;	
							      						}
							      						else{
							      							
							      						}
							      						
							      						System.out.println("personal_id:"+personal_id);
						               			break;
						               			
						               		case 1:
						               					title_th = cell.getStringCellValue().replaceAll("\\s+","");
						               					System.out.println("title_th:"+title_th);
						               			break;	
						               			
						               		case 2 :
						               					name_th = cell.getStringCellValue().replaceAll("\\s+","");
						               					System.out.println("name_th:"+name_th);
						               			break;
						               		case 3 :
						       							surname_th = cell.getStringCellValue().replaceAll("\\s+","");
						       							System.out.println("surname_th:"+surname_th);
						               			break;
						               		case 4 :
						               					name_en = cell.getStringCellValue().replaceAll("\\s+","");
						               					System.out.println("name_en:"+name_en);
						               			break;
						               		case 5 :
						               					surname_en  = cell.getStringCellValue().replaceAll("\\s+","");
						               					System.out.println("surname_en:"+surname_en);
						               			break;
						               		case 6 :
						               					gender = cell.getStringCellValue().replaceAll("\\s+","");
						               					char[] gen = gender.toCharArray();
						               					gender = gen[0]+"";
						               					System.out.println("gender from excel :"+gender+":");
						               			break;
						               		case 7 :
						              					marital_status = cell.getStringCellValue();
						              					System.out.println("marital_status:"+marital_status);
						               			break;
						               		case 8 :
						               			//birthdate 
					                   			if (HSSFDateUtil.isCellDateFormatted(cell)) {
												
					                              Date birthdate_in=  cell.getDateCellValue();
					                              int year_be = birthdate_in.getYear();
					                              birthdate_in.setYear(year_be-543);         
					                              String bd_string = birthdate_in.toString();
					                            
					                          
					                          	  birthdate =  "'"+sdf.format(birthdate_in)+"'";
					                          	  
					                              
					                              
					                            }
					                   				System.out.println("brithdate:"+birthdate);
						               			break;
						               		case 9 :
						               					address = cell.getStringCellValue();
						               					System.out.println("address:"+address);
						               			break;
						               		case 10 :
						               					try{
						               					tel = String.valueOf(cell.getStringCellValue());
						               					System.out.println("tel:"+tel);
						               					}catch(Exception X){
						               						tel = String.valueOf(cell.getNumericCellValue());
							                   				String [] tel_temp = tel.split(Pattern.quote("."));
							                   				
							                   				tel = tel_temp[0];
							                   			
						               						
						               					}
						               			break;
						               		case 11 :
						               					email = cell.getStringCellValue();
						               					System.out.println("email:"+email);
						               			break;
						               		case 12 :	
							               				facebook = cell.getStringCellValue();
							               				System.out.println("facebook:"+facebook);
						               			break;
						               		case 13 :
							               				father_name = cell.getStringCellValue();
							               				System.out.println("father_name:"+father_name);
						               			break;
						               		case 14 :
							               				father_address = cell.getStringCellValue();
							               				System.out.println("father_address:"+father_address);
						               			break;
						               		case 15 :
						               			try{
					               					father_tel = String.valueOf(cell.getStringCellValue());
					               			
					               					}catch(Exception X){
					               						father_tel = String.valueOf(cell.getNumericCellValue());
						                   				String [] father_tel_temp = father_tel.split(Pattern.quote("."));
						                   				
						                   				father_tel = father_tel_temp[0];
						                   			
					               						
					               					}
							               				System.out.println("father_tel:"+father_tel);
						               			break;
						               		case 16 :
							               				mother_name = cell.getStringCellValue();
							               				System.out.println("mother_name:"+mother_name);
						               			break;
						               		case 17 :
							               				mother_address = cell.getStringCellValue();
							               				System.out.println("mother_address:"+mother_address);
						               			break;
						               		case 18 :
						               			try{
					               					mother_tel = String.valueOf(cell.getStringCellValue());
					               					System.out.println("mothrt_tel:"+mother_tel);
					               					}catch(Exception X){
					               						mother_tel = String.valueOf(cell.getNumericCellValue());
						                   				String [] mother_tel_temp = mother_tel.split(Pattern.quote("."));
						                   				
						                   				mother_tel = mother_tel_temp[0];
						                   			
					               						
					               					}
						               				
							               				
						               			break;
						               		case 19 :
							               				parent_name = cell.getStringCellValue();
							               				System.out.println("parent_name:"+parent_name);
						               			break;
						               			
						               		case 20 :
							               				parent_address = cell.getStringCellValue();
							               				System.out.println("parent_address:"+parent_address);
						               			break;
						               		case 21 :
						               			try{
					               					parent_tel = String.valueOf(cell.getStringCellValue());
					               					System.out.println("parent_tel:"+parent_tel);
					               					}catch(Exception X){
					               						parent_tel = String.valueOf(cell.getNumericCellValue());
						                   				String [] parent_tel_temp = parent_tel.split(Pattern.quote("."));
						                   				
						                   				parent_tel = parent_tel_temp[0];
						                   			
					               						
					               					}
						               			break;
						               		case 22 :
						               			try {
							               				student_id = String.valueOf(cell.getStringCellValue()).replaceAll("\\s+","");
						               			}catch(Exception e) {
						               				student_id = String.valueOf(cell.getNumericCellValue());
						               				System.out.println("student id 1:"+student_id);///////////try//////////
						               				
						               				String[] student_id_temp = 	student_id.split(Pattern.quote("."));
						               				System.out.println("student id temp 2:"+student_id_temp[0]);///////////try//////////
						               				student_id = student_id_temp[0];
						               				
						               			}
							               				System.out.println("student_id:"+student_id);
						               			
						               			break;
						               		case 23 :
						               			
						               				if (HSSFDateUtil.isCellDateFormatted(cell)) {
													
						                              Date start_date_in=  cell.getDateCellValue();
						                              String sd_string = start_date_in.toString();
						                              
						                              
						                              
						                              
						                              
						                              int year_be = start_date_in.getYear();
						                           	  start_date_in.setYear(year_be-543);   
						                           	 
						                           	  
						                           	  Date end_date_in  =  new Date();
						                           	  	   end_date_in.setDate(start_date_in.getDate());
						                           	  	   end_date_in.setMonth(start_date_in.getMonth());
						                           	  	   end_date_in.setYear(year_be-543+8);
						                               	  	   
						                          	  start_date =  sdf.format(start_date_in);
						                          	  end_date = sdf.format(end_date_in);
						                            	
						                           	  System.out.println("Case 23");	   
						                              System.out.println("start_date:"+start_date);
						                              System.out.println("end_date:"+end_date);
						             
						                            }
							               			
						               			break;
						               		case 24 :
							               				entrance = cell.getStringCellValue().replaceAll("\\s+","");
							               				System.out.println("entrance:"+entrance);
						               			break;
						               		case 25 :	
							               				degree = cell.getStringCellValue().replaceAll("\\s+","");
							               				System.out.println("degree:"+degree);
						               			break;
						               		case 26 :
							               				school = cell.getStringCellValue();
							               				System.out.println("school:"+school);
						               			break;
						               		case 27 :
						               				 try{
							               				gpa  = cell.getNumericCellValue();
							               				System.out.println("gpa:"+gpa);
						               				 }
							               				catch(Exception x){
							               				gpa = 0;
							               					
							               				}
						               			break;
						               		case 28 :
						               					lecturer_name_th = cell.getStringCellValue().replaceAll("\\s+","");
						               					System.out.println("lecturer_name_th:"+lecturer_name_th);
																					               			
						               			break;
						               			
						               		case 29:
								               			lecturer_surname_th = cell.getStringCellValue().replaceAll("\\s+","");
						               					System.out.println("lecturer_surname_th:"+lecturer_surname_th);
						               			break;
						               			
						               		case 30 :
								               			status = cell.getStringCellValue().replaceAll("\\s+","");
							               				System.out.println("status:"+status);
								               				
						               			break;
						               		case 31 :
						               					department_str_input = cell.getStringCellValue().replaceAll("\\s+","");
						               			
						               			break;
						               		case 32:
						               					curriculum_str_input = cell.getStringCellValue();
						               					
						               			break;
						               		case 33:
						               			try {
						               				curriculum_year = String.valueOf(cell.getStringCellValue()).replaceAll("\\s+","");
					               				}catch(Exception e) {
					               					
					               				curriculum_year = String.valueOf(cell.getNumericCellValue());
					               			
					               				String[] curriculum_year_temp = curriculum_year.split(Pattern.quote("."));
					               				
					               				curriculum_year = curriculum_year_temp[0];
					               				
					               			}
						               				System.out.println("curruculum_year:"+curriculum_year);		
						               			
						               			
						               			break;
						               			
						                 }
				                  
				                    	 
					                num_col++;	 
				                }// end cellIterator(hasnext);
				                num_col = 0;
				                num_row ++;
				                
				                
				                switch(title_th){
									
									case "นาย" : title_en ="Mr."; break;
									case "นางสาว" : title_en ="Miss"; break;
									case "นาง" : title_en="Mrs."; break;
								
								}
				                
				                entrance_id = "";
				                lecturer_id = "";
				                department_id="";
				                curriculum_id = "";
				              	full_name_id ="";
				                
				                
				                System.out.println("Phase1 Parameter");
				                System.out.println("name_th:"+name_th);
								System.out.println("surname_th:"+surname_th);
								String key_index_temp="";
								String sql_cmd_phase_1 = " SELECT P.index "
										+ " FROM people P, full_name N "
										+ " WHERE P.index=N.index "
										+ " AND N.name_th='"+name_th+"'"
										+ " AND N.surname_th='"+surname_th+"'";
								
				                ///////////////////Phase 1 // Check Existing data in People table 
								ResultSet rs_people =	connect.createStatement().executeQuery(" SELECT P.index "
																				+ " FROM people P, full_name N "
																				+ " WHERE P.index=N.index "
																				+ " AND N.name_th='"+name_th+"'"
																				+ " AND N.surname_th='"+surname_th+"'");
				                
									

				                
								rs_people.last();
								if(rs_people.getRow() == 1) {	
									System.out.println("This people already exist in People Table");
									key_index_temp = rs_people.getString("index");
									
									//////////////Check Existing data in Student table 
									ResultSet rs_old_student = connect.createStatement().executeQuery(" SELECT student_id "
																									 + " FROM student "
																									 + " WHERE student_id='"+student_id+"'");
									rs_old_student.last();
									if(rs_old_student.getRow()==1)
									{
										System.out.println("This person already exist in Student table");
										
										// 
									}else{
										// Add New Student 
										// Phase A get status_id  in status table
										try{
											String sql_cmd_phase_a = " SELECT status_id  "
													+ " FROM status "
													+ " WHERE status_name='" + status +"'";
											
											ResultSet rs_status = connect.createStatement().executeQuery(" SELECT status_id  "
													+ " FROM status "
													+ " WHERE status_name='" + status +"'" );
														rs_status.last();
														if(rs_status.getRow() == 1) {
															
															System.out.println("This status already exist");
															status_id  = rs_status.getString("status_id");
															System.out.println("Phase A Found Status:"+status_id);
															
															
														}else{
															fail_list.add(num_row+1+"");
															error_log.add("row:"+(num_row+1)+"Phase A error : not found status name :"+sql_cmd_phase_a);
															// Bug status name not existing in table must add by admin before this
																
														}
														
										
										}catch(Exception xx){
											error_log.add("row:"+(num_row+1)+"Phase A catch Exception");
											fail_list.add(num_row+1+"");
											System.out.println("Error At Phase A Check  Status"+xx);
											
										}//End Phase A
										
										//Phase B get department_id from department table
										
										try{
											System.out.println("Phase B department_str_input:"+department_str_input);
											ResultSet rs_department = connect.createStatement().executeQuery(" SELECT department_id"
													+ " FROM department "
													+ " WHERE department_name_short='" + department_str_input +"'" );
											rs_department.last();
											if(rs_department.getRow() == 1) {
												department_id  = rs_department.getString("department_id");
												
												System.out.println("Phase B Found Department_id:"+department_id);
												
											}else{
												System.out.println("Phase B error");
												fail_list.add(num_row+1+"");
												error_log.add("row:"+(num_row+1)+"Phase B error not found department");
												// Bug doesn't have department this in table
													
												
											}
											
											
										}catch(Exception q){ 
											error_log.add("row:"+(num_row+1)+"Phase B catch Exception");
											fail_list.add(num_row+1+"");
											System.out.println("Error occer at Phase B:"+q);
										}
										//End Phase B
										// Phase C getv curriculum_id from curriculum table
										try{
											System.out.println("Phase C curriculum_str_input:"+curriculum_str_input);
											
											String sql_cmd_phase_c = " SELECT curriculum_id  "
													+ " FROM curriculum "
													+ " WHERE curriculum_name_en='" + curriculum_str_input +"'"
													+ " AND year='"+curriculum_year+"'";
											
											ResultSet rs_curr = connect.createStatement().executeQuery(" SELECT curriculum_id  "
													+ " FROM curriculum "
													+ " WHERE curriculum_name_en='" + curriculum_str_input +"'"
													+ " AND year='"+curriculum_year+"'");
											rs_curr.last();
											if(rs_curr.getRow() == 1) {
												curriculum_id  = rs_curr.getString("curriculum_id");
												System.out.println("Phase C Found curriculum_id:"+curriculum_id);
												
											}else{
												System.out.println("Phase C error");
												fail_list.add(num_row+1+"");
												error_log.add("row:"+(num_row+1)+"Phase C error not found currirulum :"+sql_cmd_phase_c);
												// Bug doesn't have this curriculum  in table
													
												
											}
					
										}catch(Exception ss){
											System.out.println("Error occer at Phase C:"+ss);
											error_log.add("row:"+(num_row+1)+"Phase C Exception");
											fail_list.add(num_row+1+"");
											
										}
										//END Phase C

										//Phase D get Entrance ID 
										try{
											System.out.println("Phase D entrance:"+entrance);
											String sql_cmd_phase_d = " SELECT entrance_id "
													+ " FROM entrance "
													+ " WHERE entrance_name='" + entrance +"'";
											
											ResultSet rs_ent = connect.createStatement().executeQuery(" SELECT entrance_id "
													+ " FROM entrance "
													+ " WHERE entrance_name='" + entrance +"'" );
											rs_ent.last();
											if(rs_ent.getRow() == 1) {
												entrance_id  = rs_ent.getString("entrance_id");
												System.out.println("Phase D Found entrance_id:"+entrance_id);
												
											}else{
												System.out.println("Phase D error");
												fail_list.add(num_row+1+"");
												error_log.add("row:"+(num_row+1)+"Phase D not found entrance :"+sql_cmd_phase_d);
												// Bug doesn't have this entrance  in table
													
												
											}
											
										}catch(Exception o){
											System.out.println("Error occer Phase D	:"+o);
											error_log.add("row:"+(num_row+1)+"Phase D Exception");
											
											fail_list.add(num_row+1+"");
											
										}
										// END Phase D
										
										//Phase E 
										try{
											System.out.println("Phase E");
											String sql_cmd_phase_e = " SELECT L.index "
													+ " FROM lecturer L , full_name N "
													+ " WHERE L.index = N.index AND N.name_th='"+lecturer_name_th+"' AND N.surname_th='"+lecturer_surname_th+"'";
											ResultSet rs_lec = connect.createStatement().executeQuery(" SELECT L.index "
													+ " FROM lecturer L , full_name N "
													+ " WHERE L.index = N.index AND N.name_th='"+lecturer_name_th+"' AND N.surname_th='"+lecturer_surname_th+"'");
											rs_lec.last();
											if(rs_lec.getRow() == 1) {
												lecturer_id  = rs_lec.getString("index");
												System.out.println("Phase F Found lecturer_id:"+lecturer_id);
												
											
												// Phase F Add to student table
												try{
													
													System.out.println("Data Before Add Student");
													System.out.println("Entrance_id:"+entrance_id);
													System.out.println("Curriculum id:"+curriculum_id);
													System.out.println("Department_id:"+department_id);
													System.out.println("StudentId:"+student_id);
													System.out.println("lecturer_id:"+lecturer_id);
													System.out.println("Status id :"+status_id);
													System.out.println("start_date:"+start_date);	
													System.out.println("end_date:"+end_date);
													
													String sql_stu = "INSERT INTO `student`(`index`,`student_id`,"+
															" `picture`, `start_date`, `entrance_id`, `faculty_id`,`department_id`, `gpa`, `lecturer_id`,"+
															" `curriculum_id`, `status_id`, `school`, `gpax`, `father_name`,`father_address`, `father_phone`,"+
															" `mother_name`, `mother_address`, `mother_phone`, `parent_name`, `parent_address`, `parent_phone`,`end_date`)"
															+" VALUES ('"+key_index_temp+"','"+student_id+"'," + picture + ", '" + start_date + "', '"
															+ entrance_id + "', '" + faculty_id +"', "+ department_id + ", '" + gpa + "', '" 
															+ lecturer_id + "', "+ curriculum_id + ", '" + status_id + "', '" + school + "', '"
															+ gpa +"', '"+father_name + "', '" + father_address + "', '" + father_tel + "', '" 
															+ mother_name + "', '" + mother_address + "', '" + mother_tel + "', '" 
															+ parent_name + "', '" + parent_address + "', '"+ parent_tel + "', '"+ end_date +"')";
													connect.createStatement().executeUpdate(sql_stu);
													
													/* Log file */
													String log = strUser+" "+name+" "+surname+" import student";
													Log.writeUserFile(log);
													Log.writeUserFile(sql_stu);
													
												student_added ++ ; 
													
												}catch(Exception z){
													System.out.println("Error At Phase F Add Student"+z);
													error_log.add("row:"+(num_row+1)+"Phase F Exception");
													fail_list.add(num_row+1+"");
												
													
												}
												//END PHASE F	
												
												
												
											}else{
												System.out.println("Phase E error");
												fail_list.add(num_row+1+"");
												error_log.add("row:"+(num_row+1)+"Phase E error not found lecturer :"+sql_cmd_phase_e);
												// Bug doesn't have this curriculum  in table
												
												
											}
											
											
											
											
										}catch(Exception o){
											
											System.out.println("Error occer Phase E	:"+o);
											fail_list.add(num_row+1+"");
											error_log.add("row:"+(num_row+1)+"Phase E Exception");
										}
										//
										
										
																				
										
										
									}//END else New Student
									
									
									
									
								}else{
									System.out.println("Not Existing in People Table");
									
									
									String key_index = KeyGen.genTimeStamp();
									
									/////////////////////Phase 2 // Add full_name to full_name table
									System.out.println("Phase 2");
									System.out.println("key_index:"+key_index);
									System.out.println("Data before create full_name query");
									System.out.println(personal_id);
									System.out.println(title_th);
									System.out.println(title_en);
									System.out.println(name_th);
									System.out.println(name_en);
									System.out.println(surname_th);
									System.out.println(surname_en);
									System.out.println("gender:"+gender+":");
									System.out.println(date_now.toString());
									String sql_cmd_phase_2 = " INSERT INTO `full_name`(`index`,"+
											   " `title_th`, `title_en`, `name_th`, `name_en`, `surname_th`, "+
											   " `surname_en`, `date`) "+
											   " VALUES ('"+key_index+"',"+"'"+title_th+"', " +"'"+title_en+"', "+
											   " '"+name_th+"', "+"'"+name_en+"', "+ "'"+surname_th+"', "+"'"+surname_en+"', "+
											   " '"+date_now+"')";
									
								
										
									try{
										String sql_ful = " INSERT INTO `full_name`(`index`,"+
												   " `title_th`, `title_en`, `name_th`, `name_en`, `surname_th`, "+
												   " `surname_en`, `date`) "+
												   " VALUES ('"+key_index+"',"+"'"+title_th+"', " +"'"+title_en+"', "+
												   " '"+name_th+"', "+"'"+name_en+"', "+ "'"+surname_th+"', "+"'"+surname_en+"', "+
												   " '"+date_now+"')";
										
										PreparedStatement pstmt = connect.prepareStatement(sql_ful, Statement.RETURN_GENERATED_KEYS);  
										pstmt.executeUpdate();  
										ResultSet keys = pstmt.getGeneratedKeys();    
										keys.next();  
										int key = keys.getInt(1);
										
										full_name_id = Integer.toString(key);
										 
										full_name_added ++;
										
										/* Log file */
										String log = strUser+" "+name+" "+surname+" import student";
										Log.writeUserFile(log);
										Log.writeUserFile(sql_ful);
										
										System.out.println("Phase 2 Success with full_name_id :"+ key);
										
									
									}catch(Exception e){
										System.out.println("Error At Phase 2 Add Full_name:"+e);
										error_log.add("row:"+(num_row+1)+"Phase 2 Exception :"+e+" : "+sql_cmd_phase_2);
										fail_list.add(num_row+1+"");
										
									
									}; ///////////////////////////// End Phase 2 					
									
										
										// Phase 3 Add to people table 
										System.out.println("Data before Add People Table");	
										System.out.println("key_index:"+key_index);
										System.out.println("personalId:"+personal_id);
										System.out.println("ful_name_id:"+full_name_id);
										System.out.println("gender:"+gender);
										System.out.println("marital_status:"+marital_status);
										System.out.println("date_of_birth"+birthdate.toString());
										System.out.println("phonenumber"+tel);
										System.out.println("address"+address);
										System.out.println("email"+email);
										System.out.println("facebook"+facebook);
										String sql_cmd_phase_3 = " INSERT INTO "+"`people`(`index`,`personal_id`, `full_name_id`,"+
												   " `gender`, `marital_status`, `date_of_birth`, `phone_number`,"+
												   " `address`, `email`, `facebook`)"+
												   " VALUES ('"+key_index+"','"+personal_id+"', '"+full_name_id+"', '"+gender+"', "+
												   " '"+marital_status+"', "+birthdate+", '"+tel+"', "+
												   " '"+address+"', '"+email+"', '"+facebook+"')";
										
										
									try{
										String sql_peo = " INSERT INTO "+"`people`(`index`,`personal_id`, `full_name_id`,"+
												   " `gender`, `marital_status`, `date_of_birth`, `phone_number`,"+
												   " `address`, `email`, `facebook`)"+
												   " VALUES ('"+key_index+"','"+personal_id+"', '"+full_name_id+"', '"+gender+"', "+
												   " '"+marital_status+"', "+birthdate+", '"+tel+"', "+
												   " '"+address+"', '"+email+"', '"+facebook+"')";
										connect.createStatement().executeUpdate(sql_peo);
										
										/* Log file */
										Log.writeUserFile(sql_peo);
										
										System.out.println("Phase 3 Success");
										people_added ++;
										
									}catch(Exception y){
										System.out.println("Error At Phase 3 Add People:"+y);
										error_log.add("row:"+(num_row+1)+"Phase 3 Exception :"+y+":"+sql_cmd_phase_3);
										fail_list.add(num_row+1+"");
											
									}//End Phase 3
									
									
									// Phase 4 get status_id  in status table
									try{
										String sql_cmd_phase4 = " SELECT status_id  "
												+ " FROM status "
												+ " WHERE status_name='" + status +"'";
										ResultSet rs_status =	connect.createStatement().executeQuery(" SELECT status_id  "
												+ " FROM status "
												+ " WHERE status_name='" + status +"'" );
													rs_status.last();
													if(rs_status.getRow() == 1) {
														
														System.out.println("This status already exist");
														status_id  = rs_status.getString("status_id");
														System.out.println("Phase 4 Found Status:"+status_id);
														
														
													}else{
														error_log.add("row:"+(num_row+1)+"Phase 4 Error not found status :"+sql_cmd_phase4);
										
														// Bug status name not existing in table must add by admin before this
														fail_list.add(num_row+1+"");
														
													}
													
									
									}catch(Exception xx){
										
										System.out.println("Error At Phase 4 Check  Status"+xx);
										error_log.add("row:"+(num_row+1)+"Phase 4 Exception");
										fail_list.add(num_row+1+"");
										
									}//End Phase 4
									
									//Phase 5 get department_id from department table
									
									try{
										System.out.println("Phase 5 department_str_input:"+department_str_input);
										ResultSet rs_department = connect.createStatement().executeQuery(" SELECT department_id"
												+ " FROM department "
												+ " WHERE department_name_short='" + department_str_input +"'" );
										rs_department.last();
										if(rs_department.getRow() == 1) {
											department_id  = rs_department.getString("department_id");
											
											System.out.println("Phase 5 Found Department_id:"+department_id);
											
										}else{
											System.out.println("Phase 5 error");
											fail_list.add(num_row+1+"");
											error_log.add("row:"+(num_row+1)+"Phase 5 not found department");
											// Bug doesn't have department this in table
												
											
										}
										
										
									}catch(Exception q){ 
										System.out.println("Error occer at Phase 5:"+q);
										error_log.add("row:"+(num_row+1)+"Phase 5 Exception");
										fail_list.add(num_row+1+"");
									}
									//End Phase 5
									
									// Phase 6 getv curriculum_id from curriculum table
									try{
										System.out.println("Phase 6 curriculum_str_input:"+curriculum_str_input);
										String sql_cmd_phase_6 = " SELECT curriculum_id  "
												+ " FROM curriculum "
												+ " WHERE curriculum_name_en='" + curriculum_str_input +"'"
												+ " AND year='"+curriculum_year+"'";
										
										ResultSet rs_curr = connect.createStatement().executeQuery(" SELECT curriculum_id  "
												+ " FROM curriculum "
												+ " WHERE curriculum_name_en='" + curriculum_str_input +"'" 
												+ " AND year='"+curriculum_year+"'");
										rs_curr.last();
										if(rs_curr.getRow() == 1) {
											curriculum_id  = rs_curr.getString("curriculum_id");
											System.out.println("Phase 6 Found curriculum_id:"+curriculum_id);
											
										}else{
											System.out.println("Phase 6 error");
											fail_list.add(num_row+1+"");
											error_log.add("row:"+(num_row+1)+"Phase 6 error not found curriculum :"+sql_cmd_phase_6);
											// Bug doesn't have this curriculum  in table
												
											
										}
				
									}catch(Exception ss){
										System.out.println("Error occer at Phase 6:"+ss);
										error_log.add("row:"+(num_row+1)+"Phase 6 Exception");
										fail_list.add(num_row+1+"");
									}
									//END Phase 6
									
									//Phase 7 get Entrance ID 
									try{
										System.out.println("Phase 7 entrance:"+entrance);
										String sql_cmd_phase_7 =" SELECT entrance_id "
												+ " FROM entrance "
												+ " WHERE entrance_name='" + entrance +"'";
										
										ResultSet rs_ent = connect.createStatement().executeQuery(" SELECT entrance_id "
												+ " FROM entrance "
												+ " WHERE entrance_name='" + entrance +"'" );
										rs_ent.last();
										if(rs_ent.getRow() == 1) {
											entrance_id  = rs_ent.getString("entrance_id");
											System.out.println("Phase 7 Found entrance_id:"+entrance_id);
											
										}else{
											System.out.println("Phase 7 error");
											fail_list.add(num_row+1+"");
											error_log.add("row:"+(num_row+1)+"Phase 7 not found entrance_id :"+sql_cmd_phase_7);
											// Bug doesn't have this entrance  in table
												
											
										}
										
									}catch(Exception o){
										System.out.println("Error occer Phase 7	:"+o);
										error_log.add("row:"+(num_row+1)+"Phase 7 Exception");
										fail_list.add(num_row+1+"");
									}
									// END Phase 7 
									//Phase 8
									
										try{
											System.out.println("Phase 8");
											String sql_cmd_phase_8 = " SELECT L.index "
													+ " FROM lecturer L , full_name N "
													+ " WHERE L.index = N.index AND N.name_th='"+lecturer_name_th+"' AND N.surname_th='"+lecturer_surname_th+"'";
											
											ResultSet rs_lec = connect.createStatement().executeQuery(" SELECT L.index "
													+ " FROM lecturer L , full_name N "
													+ " WHERE L.index = N.index AND N.name_th='"+lecturer_name_th+"' AND N.surname_th='"+lecturer_surname_th+"'");
											rs_lec.last();
											if(rs_lec.getRow() == 1) {
												lecturer_id  = rs_lec.getString("index");
												System.out.println("Phase 9 Found lecturer_id:"+lecturer_id);
												
												// Phase 9 Add to student table
												try{
													
													System.out.println("Data Before Add Student");
													System.out.println("Entrance_id:"+entrance_id);
													System.out.println("Curriculum id:"+curriculum_id);
													System.out.println("Department_id:"+department_id);
													System.out.println("StudentId:"+student_id);
													System.out.println("lecturer_id:"+lecturer_id);
													System.out.println("Status id :"+status_id);
													System.out.println("start_date:"+start_date);	
													System.out.println("end_date:"+end_date);
													
													
													String sql_stu = "INSERT INTO `student`(`index`,`student_id`,"+
															" `picture`, `start_date`, `entrance_id`, `faculty_id`,`department_id`, `gpa`, `lecturer_id`,"+
															" `curriculum_id`, `status_id`, `school`, `gpax`, `father_name`,`father_address`, `father_phone`,"+
															" `mother_name`, `mother_address`, `mother_phone`, `parent_name`, `parent_address`, `parent_phone`,`end_date`)"
															+" VALUES ('"+key_index+"','"+student_id+"'," + picture + ", '" + start_date + "', '"
															+ entrance_id + "', '" + faculty_id +"', "+ department_id + ", '" + gpa + "', '" 
															+ lecturer_id + "', "+ curriculum_id + ", '" + status_id + "', '" + school + "', '"
															+ gpa +"', '"+father_name + "', '" + father_address + "', '" + father_tel + "', '" 
															+ mother_name + "', '" + mother_address + "', '" + mother_tel + "', '" 
															+ parent_name + "', '" + parent_address + "', '"+ parent_tel + "', '"+ end_date +"')";
													connect.createStatement().executeUpdate(sql_stu);
													
													/* Log file */
													Log.writeUserFile(sql_stu);
													
												student_added ++ ; 
													
												}catch(Exception z){
													System.out.println("Error At Phase 9 Add Student"+z);
													fail_list.add(num_row+1+"");
													error_log.add("row:"+(num_row+1)+"Phase 9 Exception");
													
												}
												//END PHASE 9
												
												
												
											}else{
												// lecturer not found
												fail_list.add(num_row+1+"");
												error_log.add("row:"+(num_row+1)+"Phase 8 error not found lecturer :"+sql_cmd_phase_8);
												
												
												}
											
										}catch(Exception q){
											System.out.println("Phase 8 Error Occer:"+q);
											fail_list.add(num_row+1+"");
											error_log.add("row:"+(num_row+1)+"Phase 8 Exception");
										}	
									//end Phase
									
								
								}	// END if Phase 1 
								
		            	} catch(Exception getData){ //end try Row row = rowIterator.next();
		            		System.out.println("Erroe cant't read Excel:"+getData);
		            		num_col = 0;
		            		fail_list.add(num_row+1+"");
		            		num_row++;
		            	}
		                
		                
		            } // END while(rowIterator.hasNext())
		            	
		    	
            file.close();
            System.out.println("fail_list size :"+fail_list.size());
            for(int num_fail= 0;num_fail<fail_list.size();num_fail++)
            {
            	System.out.println("Fail to import row:"+fail_list.get(num_fail).toString());
            }
            for(int temp_num = 0;temp_num<error_log.size();temp_num++)
            {
            	System.out.println(error_log.get(temp_num).toString());
            }
            System.out.println("full_name_added:"+full_name_added);
            System.out.println("people_added:"+people_added);
            System.out.println("student_added:"+student_added);
           
            String fail_list_all ="";
            if(fail_list.size()==0)
            {
            	fail_list_all ="";
            }
            else{
            	  fail_list_all = "ข้อมูลผิดพลาดในแถวที่ :";
            	  for(int x=0 ;x<fail_list.size();x++)
                  {
            		 
                  	fail_list_all = fail_list_all+fail_list.get(x).toString()+" , ";
                  }
            	  fail_list_all = fail_list_all + " \n"+ "กรุณาตรวจสอบข้อมูลดังกล่าว ก่อนนำเข้าอีกครั้ง";
            }
          
    		out.print("success:\n"
					+people_added+" to people table.  \n"
					+ student_added +" to student table.  \n"
					+ full_name_added + " to full name table.  \n"
					+fail_list_all);
			
		}//end try fileinputstream
		catch(Exception x){
			System.out.println(x);
			
			out.print("fail");
		}

		connect.close();

%>